﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Ylh.DBUtility;
using Ylh.IDAL;
using Ylh.Model;
using System.Data;
using System.Data.SqlClient;




namespace Ylh.SQLServerDAL
{
    public class CustMettingDAL : ICustMetting
    {

        /// <summary>
        /// 增加一条数据
        /// </summary>
       public int InsertMettingInfo( string ConStr, Ylh.Model.TbCustMetting model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into TbCustMetting(");
            strSql.Append("CustID,StaffID,Accompany,Remark,MetingTime)");
            strSql.Append(" values (");
            strSql.Append("@CustID,@StaffID,@Accompany,@Remark,@MetingTime)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@CustID", SqlDbType.Int,4),
					new SqlParameter("@StaffID", SqlDbType.Int,4),
					new SqlParameter("@Accompany", SqlDbType.VarChar,40),
					new SqlParameter("@Remark", SqlDbType.Text),
                                        new SqlParameter("@MetingTime", SqlDbType.DateTime)};
            parameters[0].Value = model.CustID;
            parameters[1].Value = model.StaffID;
            parameters[2].Value = model.Accompany;
            parameters[3].Value = model.Remark;
            parameters[4].Value = model.MetingTime;
 DataTable dt = SqlHelper.ExcuteQuery(ConStr, strSql.ToString(), parameters, CommandType.Text);

            return Convert.ToInt32(dt.Rows[0][0]);
         
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
       public bool UpdateMettingInfo(string ConStr, Ylh.Model.TbCustMetting model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update TbCustMetting set ");
            strSql.Append("CustID=@CustID,");
            strSql.Append("StaffID=@StaffID,");
            strSql.Append("Accompany=@Accompany,");
            strSql.Append("Remark=@Remark,");
            strSql.Append("MetingTime=@MetingTime");
            strSql.Append(" where CustMettingID=@CustMettingID ");
            SqlParameter[] parameters = {
					new SqlParameter("@CustMettingID", SqlDbType.Int,4),
					new SqlParameter("@CustID", SqlDbType.Int,4),
					new SqlParameter("@StaffID", SqlDbType.Int,4),
					new SqlParameter("@Accompany", SqlDbType.VarChar,40),
					new SqlParameter("@Remark", SqlDbType.Text),
                                        new SqlParameter("@MetingTime", SqlDbType.DateTime)};
            parameters[0].Value = model.CustMettingID;
            parameters[1].Value = model.CustID;
            parameters[2].Value = model.StaffID;
            parameters[3].Value = model.Accompany;
            parameters[4].Value = model.Remark;
            parameters[5].Value = model.MetingTime;
 int i = SqlHelper.ExecuteNonQuery(ConStr, strSql.ToString(), parameters, CommandType.Text);

            if (i > 0) return true;
            return false;
            
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
       public bool Delete(string ConStr, int CustMettingID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete TbCustMetting ");
            strSql.Append(" where CustMettingID=@CustMettingID ");
            SqlParameter[] parameters = {
					new SqlParameter("@CustMettingID", SqlDbType.Int,4)};
            parameters[0].Value = CustMettingID;

             int i = SqlHelper.ExecuteNonQuery(ConStr, strSql.ToString(), parameters, CommandType.Text);

            if (i > 0) return true;
            return false;
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Ylh.Model.TbCustMetting GetModel( string ConStr, int CustMettingID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 CustMettingID,CustID,StaffID,Accompany,Remark,MetingTime from TbCustMetting ");
            strSql.Append(" where CustMettingID=@CustMettingID ");
            SqlParameter[] parameters = {
					new SqlParameter("@CustMettingID", SqlDbType.Int,4)};
            parameters[0].Value = CustMettingID;

            Ylh.Model.TbCustMetting model = new Ylh.Model.TbCustMetting();

            DataTable dt = SqlHelper.ExcuteQuery(ConStr, strSql.ToString(), parameters, CommandType.Text);

            if (dt.Rows.Count > 0)
            {
                if (dt.Rows[0]["CustMettingID"].ToString() != "")
                {
                    model.CustMettingID = int.Parse(dt.Rows[0]["CustMettingID"].ToString());
                }
                if (dt.Rows[0]["CustID"].ToString() != "")
                {
                    model.CustID = int.Parse(dt.Rows[0]["CustID"].ToString());
                }
                if (dt.Rows[0]["StaffID"].ToString() != "")
                {
                    model.StaffID = int.Parse(dt.Rows[0]["StaffID"].ToString());
                }
                model.Accompany = dt.Rows[0]["Accompany"].ToString();
                model.Remark = dt.Rows[0]["Remark"].ToString();

                if (dt.Rows[0]["MetingTime"].ToString() != "")
                {
                    model.MetingTime =Convert.ToDateTime( dt.Rows[0]["MetingTime"]);
                }


                return model;
            }
            else
            {
                return null;
            }
        }


        public DataTable GetMettingInfo(string ConStr, string StaffID, string CustID)
        {
            string sql = @"SELECT * FROM TbCustMetting
LEFT JOIN TbCust on TbCust.CustID =TbCustMetting.CustID where TbCustMetting.CustID=" + CustID + " AND StaffID=" + StaffID;
            return SqlHelper.ExcuteQuery(ConStr, sql, CommandType.Text);
        }


        public bool GetCountmetting(string ConStr, string StaffID, string CustID)
        {
            string sql = @"SELECT count(CustMettingID) FROM TbCustMetting where CustID=" + CustID + " and StaffID =" + StaffID;

            return Convert.ToInt32( SqlHelper.ExcuteQuery(ConStr, sql, CommandType.Text).Rows[0][0])>0;
        }

    }
}
